package sy.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.apache.log4j.Logger;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.dlbj.util.DBUtil;
import sy.util.EMailUtil;
import sy.dao.BaseDaoI;
import sy.pageModel.Customer;
import sy.pageModel.DataGrid;
import sy.pageModel.Product;
import sy.service.ProductServiceI;

@Service("productService")
public class ProductServiceImpl implements ProductServiceI {
	
	public static final String NO_SENT_INFO = "未发送邀请邮件";

	private static final Logger logger = Logger.getLogger(ProductServiceImpl.class);

	//private BaseDaoI<Tproduct> productDao;

	/*@Autowired
	public void setProductDao(BaseDaoI<Tproduct> productDao) {
		this.productDao = productDao;
	}*/

	@Override
	public Product save(Product product) throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			Statement stmt = conn.createStatement();
			String sql = "select max(CAST(id AS SIGNED)) from z_ryhy_zk";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			int id = 0;
			if(rs.next()) {
				id = rs.getInt(1) + 1;
				System.out.println(id);
			} else {
				rs.close();
			    stmt.close();
				throw new Exception("获取id失败");
			}
		    rs.close();
		    String xm = product.getXm();
		    if(xm != null) {
		    	xm = xm.trim();
		    }
		    String sjh = product.getSjh();
		    if(sjh != null) {
		    	sjh = sjh.trim();
		    }
		    sql = "select sjh from z_ryhy_zk where (xm='" + xm + "' and sjh='" + sjh + "') and frozen='N'";
		    System.out.println(sql);
		    rs = stmt.executeQuery(sql);
		    if(rs.next()) {
		        System.out.println("荣誉会员已存在!");
		        rs.close();
		        stmt.close();
		        throw new Exception("荣誉会员已存在!");
		    }
		    product.setId(id + "");
		    String vnumber = "R" + String.valueOf(10000000 + id + 66).substring(1);
		    product.setVnumber(vnumber);
			String inviteCode = DBUtil.genInviteCode(id);
			product.setInviteCode(inviteCode);
			product.setEmailSentInfo(NO_SENT_INFO);
		    sql = "insert into z_ryhy_zk(id,xm,sjh,email,invite_code,vnumber,tjr,email_sent_info,show_name,honor) values(?,?,?,?,?,?,?,?,?,?)";
		    System.out.println(sql);
		    PreparedStatement preStmt = conn.prepareStatement(sql);
		    preStmt.setString(1, product.getId());
		    preStmt.setString(2, product.getXm());
		    preStmt.setString(3, product.getSjh());
		    preStmt.setString(4, product.getEmail());
		    preStmt.setString(5, product.getInviteCode());
		    preStmt.setString(6, vnumber);
		    preStmt.setString(7, product.getTjr());
		    preStmt.setString(8, product.getEmailSentInfo());
		    preStmt.setInt(9, product.getShowName() ? 1 : 0);
		    preStmt.setInt(10, product.getHonor());
		    preStmt.executeUpdate();		    
		    preStmt.close();
		    return product;
		} catch(Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
			throw e;
		} finally {
			try {
				conn.close();
			} catch(Exception e) {
				
			}
		}
	}

	@Override
	public DataGrid datagrid(Product product) {
		DataGrid dg = new DataGrid();		
		List<Product> nl = new ArrayList<Product>();
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "select count(*) from z_ryhy_zk where frozen='N'";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next()) {
            	dg.setTotal(rs.getLong(1));
            }
            rs.close();
            stmt.close();
		    sql = "select id,xm,sjh,email,invite_code,state,tjr,email_sent_info,vnumber,show_name,honor from z_ryhy_zk where frozen='N'";
		    sql = addOrder(product, sql);
		    int fromRow = (product.getPage() - 1) * product.getRows();
		    sql += " limit " + fromRow + "," + product.getRows();
		    stmt = conn.createStatement();
		    rs = stmt.executeQuery(sql);
		    while(rs.next()) {
		    	Product row = new Product(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(7), rs.getString(8), rs.getString(9), rs.getInt(10) == 1 ? true : false, rs.getInt(11));
		    	if(rs.getInt(6) != 0) {
		    		row.setIsActive(true);
		    	}
		    	nl.add(row);
		    }
		    rs.close();
		    stmt.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch(Exception e) {
				
			}
		}
		
		//dg.setTotal((long)nl.size());
		dg.setRows(nl);
		return dg;
	}

	/*private void changeModel(List<Tproduct> l, List<Product> nl) {
		if (l != null && l.size() > 0) {
			for (Tproduct t : l) {
				Product u = new Product();
				BeanUtils.copyProperties(t, u);
				nl.add(u);
			}
		}
	}*/

	private String addOrder(Product product, String hql) {
		if (product.getSort() != null) {
			if("isActive".equals(product.getSort())) {
				product.setSort("state");
			} else if("emailSentInfo".equals(product.getSort())) {
				product.setSort("email_sent_info");
			} else if("xm".equals(product.getSort())) {
				product.setSort("convert(xm using gbk)");
			} else if("showName".equals(product.getSort())) {
				product.setSort("show_name");
			}
			hql += " order by " + product.getSort() + " " + product.getOrder();
		}
		return hql;
	}

	@Override
	public void remove(String ids) throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String[] idArr = ids.split(",");
			String sql = "";
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			String xm = "";
			String email = "";
			String inviteCode = "";
			String tjr = "";
			for(String id : idArr) {
				sql = "select xm, email, invite_code, tjr from z_ryhy_zk where id='" + id + "'";
				rs = stmt.executeQuery(sql);
				if(rs.next()) {
					xm = rs.getString(1);
					email = rs.getString(2);
					inviteCode = rs.getString(3);
					tjr = rs.getString(4);
					System.out.println(xm + " " + email + " " + inviteCode + " " + tjr);
					rs.close();
					//发邮件
					boolean sent = EMailUtil.sendMail(new String[]{email}, "来自第六边界科技的邀请", genEmailContent(xm, inviteCode, tjr));
					String info = sent ? "发送邀请邮件成功" : "发送邀请邮件失败";
					sql = "update z_ryhy_zk set email_sent_info='" + info + "' where id='" + id + "'";
					stmt.executeUpdate(sql);
				}
			}
			stmt.close();
	    } catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				conn.close();
			} catch(Exception e) {}
		}
	}
	
	@Override
	public void frozen(String ids) throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String[] idArr = ids.split(",");
			String sql = "";
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			for(String id : idArr) {
				sql = "select state, email_sent_info from z_ryhy_zk where id='" + id + "'";
				rs = stmt.executeQuery(sql);
				if(rs.next()) {
					if(rs.getInt(1) != 0) {
						System.out.println(id + " : 已激活！");
						rs.close();
						//stmt.close();
						continue;
					}
					if("发送邀请邮件成功".equals(rs.getString(2))) {
						System.out.println(id + " : 已发送邀请！");
						rs.close();
						//stmt.close();
						continue;
					}
					rs.close();
					//stmt.close();
					sql = "update z_ryhy_zk set frozen='Y' where id='" + id + "'";
					stmt.executeUpdate(sql);
					System.out.println(id + " : frozen done!");
				}
			}
			stmt.close();
	    } catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				conn.close();
			} catch(Exception e) {}
		}
	}

	@Override
	public Product edit(Product product) throws Exception {
		System.out.println("inviteCode=" + product.getInviteCode());
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			Statement stmt0 = conn.createStatement();
			ResultSet rs = null;
			String xm = product.getXm();
		    if(xm != null) {
		    	xm = xm.trim();
		    }
		    String sjh = product.getSjh();
		    if(sjh != null) {
		    	sjh = sjh.trim();
		    }
		    String sql = "select sjh from z_ryhy_zk where xm='" + xm + "' and sjh='" + sjh + "' and frozen='N' and id <> '" + product.getId() + "'";
		    System.out.println(sql);
		    rs = stmt0.executeQuery(sql);
		    if(rs.next()) {
		        System.out.println("荣誉会员已存在!");
		        rs.close();
		        stmt0.close();
		        throw new Exception("荣誉会员已存在!");
		    }
		    rs.close();
		    stmt0.close();
		    sql = "update z_ryhy_zk set xm=?,sjh=?,email=?,tjr=?,show_name=?,honor=? where id=?";
		    PreparedStatement stmt = conn.prepareStatement(sql);
		    stmt.setString(1, product.getXm());
		    stmt.setString(2, product.getSjh());
		    stmt.setString(3, product.getEmail());
		    stmt.setString(4, product.getTjr());
		    stmt.setString(7, product.getId());
		    stmt.setInt(6, product.getHonor());
		    stmt.setInt(5, product.getShowName() ? 1 : 0);
		    stmt.executeUpdate();		    
		    stmt.close();
		    return product;
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				conn.close();
			} catch(Exception e) {
				
			}
		}
	}
	
	private String genEmailContent(String name, String inviteCode, String tjr) {
		StringBuffer sb = new StringBuffer();
		String temp1 = "";
		String temp2 = "";
		if(tjr != null && !"".equals(tjr)) {
			temp1 = "经您的朋友" + tjr + "先生推荐，";
			temp2 = "或您的推荐人" + tjr + "（" + EMailUtil.tjrPhone.get(tjr) + "）";
		}
		sb.append("<html>\n");
		sb.append("<body>\n");
		sb.append("尊敬的" + name + "：<br/><br/>");
		sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + temp1 + "我们非常荣幸的邀请您成为\"第六边界荣誉会员\"，如无异议请点击以下链接<a href='http://www.cq666666.cn/h_member.jsp?id=12&code=" + inviteCode + "'>http://www.cq666666.cn/h_member.jsp?id=12&code=" + inviteCode + "</a>进行激活。<br/><br/>");
		sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;您也可以访问我们公司的网站：http://www.cq666666.cn，进入\"会员中心\"->\"荣誉会员\"页面，输入您的邀请码：" + inviteCode + " 进行激活。<br/><br/>");
		sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;如需咨询，请致电客服电话400-153-5856" + temp2 + "。<br/><br/>");
		sb.append("<br/><div style='width:95%;text-align:right;'>重庆第六边界科技有限公司</div>");
		sb.append("</body>\n");
		sb.append("</html>\n");
		return sb.toString();
	}
}
